package com.cqeec.myblog.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cqeec.myblog.model.Album;
import com.cqeec.myblog.util.DbUtil;

public class AlbumDaoImpl implements AlbumDao {

	@Override
	public void save(Album album) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("INSERT INTO Album VALUES(?,?,?,?,?)");
		ps.setString(1, album.getId());
		ps.setString(2, album.getBlogger());
		ps.setString(3, album.getName());
		ps.setDate(4, new Date(album.getCreationTime().getTime()));
		ps.setString(5, album.getDescription());
		ps.executeUpdate();
		ps.close();
	}

	@Override
	public List<Album> findAll() throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(
				"SELECT A.*,B.quantity FROM Album AS A LEFT JOIN (SELECT album,COUNT(id) AS quantity FROM Photo GROUP BY album) AS B ON B.album=A.id");
		List<Album> list = new ArrayList<Album>();
		Album album;
		while (rs.next()) {
			album = new Album();
			album.setId(rs.getString("id"));
			album.setBlogger(rs.getString("blogger"));
			album.setName(rs.getString("name"));
			album.setCreationTime(rs.getDate("creationTime"));
			album.setDescription(rs.getString("description"));
			album.setQuantity(rs.getInt("quantity"));
			list.add(album);
		}
		rs.close();
		state.close();
		return list;
	}

	@Override
	public void delete(String id) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		// 删除相册对应的照片信息
		PreparedStatement psPhoto = conn.prepareStatement("DELETE FROM Photo WHERE album=?");
		psPhoto.setString(1, id);
		psPhoto.executeUpdate();
		psPhoto.close();
		// 删除相册
		PreparedStatement ps = conn.prepareStatement("DELETE FROM Album WHERE id=?");
		ps.setString(1, id);
		ps.executeUpdate();
		ps.close();
	}

	@Override
	public void update(String id, String name, String desc) throws ClassNotFoundException, SQLException {
		Connection conn = DbUtil.getDbConnection();
		PreparedStatement ps = conn.prepareStatement("UPDATE Album SET name=?,description=? WHERE id=?");
		ps.setString(1, name);
		ps.setString(2, desc);
		ps.setString(3, id);
		ps.executeUpdate();
		ps.close();
	}

}
